﻿#region License
/* Copyright (C) 2009 Tim Coulter
 * 
 * This file is part of ExtremeML.
 * 
 * ExtremeML is free software: you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation either version
 * 2 of the License, or (at your option) any later version.
 * 
 * ExtremeML is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty
 * of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See
 * the GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see http://www.gnu.org/licenses/.
*/
#endregion

using System.Collections.Generic;
using System.Xml.XPath;
using System.Linq;
using ExtremeML.Packaging;

namespace ExtremeML.Samples
{
	/// <summary>
	/// Demonstrates various operations that manipulate worksheet tables.
	/// </summary>
	public static class TableManipulation
	{
		/// <summary>
		/// Populates the Countries worksheet table with data from the sample DataSet.
		/// </summary>
		/// <remarks>
		/// This method demonstrates using the TableWrapper.Fill() method to populate
		/// a template worksheet table with data from a DataSet. Several overloads of
		/// this method exist, supporting DataTable, DataView and Array data, including
		/// the possibility of explicitly mapping data elements to specific table columns.
		/// </remarks>
		public static void PopulateCountriesFromDataSet()
		{
			Utility.CreateFileFromResource(Utility.TemplateWorkbookResource, Utility.TargetOutputFile);

			using (var package = SpreadsheetDocumentWrapper.Open(Utility.TargetOutputFile))
			{
				var table = package.WorkbookPart.GetTablePart("Countries").Table;
				table.Fill(Utility.GetSampleDataSet(), "Countries");
			}
		}

		/// <summary>
		/// Populates the Countries worksheet table with filtered data from the sample DataSet.
		/// </summary>
		/// <remarks>
		/// This method demonstrates using the TableWrapper.Fill() method to populate
		/// a template worksheet table with data from a filtered and sorted DataView.
		/// </remarks>
		public static void PopulateCountriesSortedAndFilterted()
		{
			Utility.CreateFileFromResource(Utility.TemplateWorkbookResource, Utility.TargetOutputFile);

			using (var package = SpreadsheetDocumentWrapper.Open(Utility.TargetOutputFile))
			{
				var table = package.WorkbookPart.GetTablePart("Countries").Table;

				var view = Utility.GetSampleDataSet().Tables["Countries"].DefaultView;
				view.Sort = "GDP ASC";
				view.RowFilter = "GDP > 1000000";

				table.Fill(view);
			}
		}

		/// <summary>
		/// Populates the Countries worksheet table in two passes.
		/// </summary>
		/// <remarks>
		/// This method demonstrates using the TableWrapper.Fill() method to
		/// populate a template worksheet table with data in two separate passes.
		/// A partially populated table can thus be extended endlessly.
		/// </remarks>
		public static void PopulateCountriesMultipleTimes()
		{
			Utility.CreateFileFromResource(Utility.TemplateWorkbookResource, Utility.TargetOutputFile);

			using (var package = SpreadsheetDocumentWrapper.Open(Utility.TargetOutputFile))
			{
				var table = package.WorkbookPart.GetTablePart("Countries").Table;

				var view = Utility.GetSampleDataSet().Tables["Countries"].DefaultView;
				view.Sort = "GDP ASC";

				view.RowFilter = "GDP < 1000";
				table.Fill(view);

				view.RowFilter = "GDP > 1000000";
				table.Fill(view);
			}
		}

		/// <summary>
		/// Populates the Countries worksheet table with data from an array created using a Linq query.
		/// </summary>
		/// <remarks>
		/// This method demonstrates using the TableWrapper.Fill() method to populate a template
		/// worksheet table with data from an array created using a Linq query. In this unmapped
		/// example, data from the array are assigned sequentially to the available table columns.
		/// </remarks>
		public static void PopulateCountriesFromLinq()
		{
			Utility.CreateFileFromResource(Utility.TemplateWorkbookResource, Utility.TargetOutputFile);

			using (var package = SpreadsheetDocumentWrapper.Open(Utility.TargetOutputFile))
			{
				var table = package.WorkbookPart.GetTablePart("Countries").Table;

				var data = (from element in Utility.GetSampleDataXml().XPathSelectElements("//Countries")
							where element.Element("Capital").Value.StartsWith("M")
							select new object[]
			                  	{
									element.Element("Country").Value,
									element.Element("Capital").Value,
									null, // Table columns can
									null, // be left unpopulated
									null, // using null values.
									long.Parse(element.Element("Population").Value),
									double.Parse(element.Element("GDP").Value)
			                  	}).ToArray();


				table.Fill(data);
			}
		}

		/// <summary>
		/// Populates the Countries worksheet table with Linq query data mapped to specific columns.
		/// </summary>
		/// <remarks>
		/// This method demonstrates using the TableWrapper.Fill() method to populate a template worksheet
		/// table with data from a Linq query, wherby data items are explictly mapped to table columns.
		/// </remarks>
		public static void PopulateCountriesExplicitMappings()
		{
			Utility.CreateFileFromResource(Utility.TemplateWorkbookResource, Utility.TargetOutputFile);

			using (var package = SpreadsheetDocumentWrapper.Open(Utility.TargetOutputFile))
			{
				var table = package.WorkbookPart.GetTablePart("Countries").Table;

				// Here we have deliberately switched the mappings of the Capital and Country columns
				var mappings = new[] { "Capital City", "Country", "Population", "GDP ($M)" };

				var data = (from element in Utility.GetSampleDataXml().XPathSelectElements("//Countries")
							select new object[]
			                  	{
									element.Element("Country").Value,
									element.Element("Capital").Value,
									long.Parse(element.Element("Population").Value),
									double.Parse(element.Element("GDP").Value)
			                  	}).ToArray();


				table.Fill(mappings, data);
			}
		}

		/// <summary>
		/// Populates the DataTypes worksheet table with random sample data.
		/// </summary>
		/// <remarks>
		/// This method demonstrates populating a table with randomly generated strongly-typed data.
		/// </remarks>
		public static void PopulateDataTypesRandom()
		{
			Utility.CreateFileFromResource(Utility.TemplateWorkbookResource, Utility.TargetOutputFile);

			using (var package = SpreadsheetDocumentWrapper.Open(Utility.TargetOutputFile))
			{
				var table = package.WorkbookPart.GetTablePart("DataTypes").Table;

				var data = new List<object[]>();

				for (var i = 0; i < 10; i++)
				{
					data.Add(new object[]
			         	{
			         		Utility.GetRandomString(),			         		
			         		Utility.GetRandomUri(),			         		
							Utility.GetRandomDateTime(),
							Utility.GetRandomTimeSpan(),
							Utility.GetRandomInteger(),
							Utility.GetRandomDouble(),
							Utility.GetRandomDecimal(),
							Utility.GetRandomBoolean()
						});
				}

				table.Fill(data.ToArray());
			}
		}
    }
}
